package com.sinopac.model.GroupManagement;

import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import netfusion.jbranch.database.DataAccessManager;

import org.apache.commons.lang.StringUtils;

import com.sinopac.entity.GroupManagement.MainAccountVO;
import com.sinopac.entity.GroupManagement.RelateAccountVO;
import com.sinopac.entity.GroupManagement.addNewGroupVO;
import com.sinopac.model.core.ModelCore;

public class GroupManagementModel extends ModelCore{
	// 傳回前端做popup的錯誤訊息秀出
	public String errorMessage;

	public boolean is_group_token;

	public List<MainAccountVO> queryMainAccountList(String custId) {
		List<MainAccountVO> mainAccount_L = new ArrayList<MainAccountVO>();
		MainAccountVO vo = new MainAccountVO();
		DataAccessManager dam = new DataAccessManager();

		try {
			String query = "select * from Customers where customer_id =:custId";
			dam.setObject("custId", custId);

			dam.setQueryString(query);
			List<Map<String, Object>> list1 = dam.exeQuery();
			for (Map<String, Object> map1 : list1) {
				vo = new MainAccountVO();
				vo.setCustId(StringUtils.trimToEmpty((String) map1.get("customer_id")));
				vo.setCustName(StringUtils.trimToEmpty((String) map1.get("customer_name")));
				vo.setAoCode(StringUtils.trimToEmpty((String) map1.get("AO_code")));
				vo.setTokenESN(StringUtils.trimToEmpty((String) map1.get("token_esn")));
				vo.setIs_group(StringUtils.trimToEmpty((String) map1.get("is_group")));
				vo.setIs_group_main(StringUtils.trimToEmpty((String) map1.get("is_group_main")));
				
				//2012-11-13修改取得token來源
				dam = new DataAccessManager();
				StringBuffer query_sb1 = new StringBuffer(" select token_esn from Token_Customer ");
				
				if(((String) map1.get("is_group")).equals("0")){
					query_sb1.append(" where related_to =:custId ");
					dam.setObject("custId", custId);
				}else{
					query_sb1.append(" where related_to =:group_id ");
					dam.setObject("group_id",  StringUtils.trimToEmpty((String) map1.get("group_id")));
				}
				dam.setQueryString(query_sb1.toString());
				List<Map<String, Object>> list2 = dam.exeQuery();
				if(!list2.isEmpty()){
					vo.setTokenESN(StringUtils.trimToEmpty((String)list2.get(0).get("token_esn")));
				}else{
					vo.setTokenESN(null);
					if(((String) map1.get("is_group")).equals("0")){
						vo.setToken_status(chkJobQs(custId));
					}else{
						vo.setToken_status(chkJobQs((String)map1.get("group_id")));
					}
				}
//				for(Map<String,Object> map2:list2){
//					vo.setTokenESN(StringUtils.trimToEmpty((String) map2.get("token_esn")));
//				}
				//2012-11-13修改取得token來源
				
				vo.setOtpStatus(StringUtils.trimToEmpty((String) map1.get("otp_status")));
				vo.setMainAcc(StringUtils.trimToEmpty((String) map1.get("is_group_main")));
				mainAccount_L.add(vo);
			}

		} catch (Exception e) {
			e.printStackTrace();
		}

		return mainAccount_L;
	}
	
	/**
	 * 確認客戶是否有申請Token
	 * true:申請中, false:新申請
	 */
	public Boolean chkJobQs(String id){
		Boolean flag = false;
		DataAccessManager dam = new DataAccessManager();
		String sql = "select * from Job_queues " +
					 "where customer_id = :id or group_id = :id"; 
		dam.setQueryString(sql);
		dam.setObject("id", id);
		try {
			List<Map<String,Object>> list = dam.exeQuery();
			if(!list.isEmpty()){
				flag = true; 
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return flag;
	}
	
	/**
	 * 更新正在申請Token的客戶，將資料改為集團戶
	 */
	public Integer updJobQs(String id, String id_ran){
		int r = 0;
		try {
			String query = "update Job_queues set group_id = :id_ran where customer_id =:id ";
			setObject("id", id);
			setObject("id_ran", id_ran);
			setQueryString(query);
			exeUpdate();
			r = exeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
		}
		return r;
	}
	
	/**
	 * 確認客戶是否已經主管核准申請Token
	 * true:已申請, false:未申請
	 */
	public Boolean chkTokenCust(String id){
		Boolean flag = false;
		DataAccessManager dam = new DataAccessManager();
		String sql = "select * from token_customer " +
					 "where related_to = :id"; 
		dam.setQueryString(sql);
		dam.setObject("id", id);
		try {
			List<Map<String,Object>> list = dam.exeQuery();
			if(!list.isEmpty()){
				flag = true; 
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return flag;
	}
	
	/**
	 * 更新已經申請Token的客戶，將Token_Customer資料改為集團戶
	 */
	public Integer updTokenCust(String id, String id_ran){
		int r = 0;
		try {
			String query = "update token_customer set is_related_to_group = '1', related_to = :id_ran where related_to = :id ";
			setObject("id", id);
			setObject("id_ran", id_ran);
			setQueryString(query);
			exeUpdate();
			r = exeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
		}
		return r;
	}

	public List<RelateAccountVO> queryRelateAccountList(String custId) {
		List<RelateAccountVO> relateAccount_L = new ArrayList<RelateAccountVO>();
		RelateAccountVO vo = new RelateAccountVO();
		DataAccessManager dam = new DataAccessManager();

		try {
			String query = " select * from Customers where customer_id !=:custId "
					+ " and group_id = (select rtrim(ltrim(group_id)) as group_id from Customers "
					+ " where customer_id =:custId and is_group = '1')";

			dam.setObject("custId", custId);

			dam.setQueryString(query);
			List<Map<String, Object>> list = dam.exeQuery();
			for (Map<String, Object> map : list) {
				vo = new RelateAccountVO();
				vo.setRelateCustId(map.get("customer_id") + "");
				vo.setRelateCustName(map.get("customer_name") + "");
				vo.setRelateAoCode(map.get("AO_code") + "");
				vo.setRelateOtpStatus(map.get("otp_status") + "");
				vo.setSetMainAcct(map.get("is_group_main") + "");
				vo.setMoveOut(map.get("otp_status") + "");

				relateAccount_L.add(vo);
			}

		} catch (Exception e) {
			e.printStackTrace();
		}

		return relateAccount_L;
	}

	public Integer deleteRelateAccountList(String relateCustId){
		int r = 0;
		try {
			String query = "update Customers set group_id = null,is_group = '0' where customer_id =:relateCustId ";
			setObject("relateCustId", relateCustId);
			setQueryString(query);
			exeUpdate();
			r = exeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
		}
		return r;

	}

	public Integer updateRelateAccountList(String relateCustId, String custId) {
		int r = 0;
		DataAccessManager dam = new DataAccessManager();
		try {

			System.out.println("relateCustId:" + relateCustId);
			System.out.println("custId:" + custId);

			// 檢查是否為集團戶
			checkGroup(dam, custId);

			// 將此客戶改為"是"主帳戶
			dam = new DataAccessManager();
			String query1 = " update Customers set is_group_main = 1 where customer_id =:relateCustId ";
			dam.setObject("relateCustId", relateCustId);
			dam.setQueryString(query1);
			dam.exeUpdate();

			// 將原主帳戶改為"否"
			dam = new DataAccessManager();
			String query2 = " update Customers set is_group_main = 0 where customer_id !=:relateCustId "
					+ " and is_group_main = 1";
			dam.setObject("relateCustId", relateCustId);
			dam.setQueryString(query2);
			dam.exeUpdate();

			// 更新Goup table的集團戶主帳戶資料
			updateGroupTable(dam, relateCustId);

		} catch (Exception e) {
			e.printStackTrace();
		}
		return r;
	}

	// 更新Goup table的集團戶主帳戶資料
	private void updateGroupTable(DataAccessManager dam, String relateCustId) {

		try {
			// 檢查group_id是否存在Groups的table中，並放入list1做暫存以便判斷。
			dam = new DataAccessManager();
			String query3 = " select group_id from Groups where group_id = "
					+ " (select group_id from Customers where customer_id =:relateCustId ) ";
			dam.setObject("relateCustId", relateCustId);
			dam.setQueryString(query3);
			List<Map<String, Object>> list1 = dam.exeQuery();

			// 撈出新的主帳戶的客戶資料，並放入List2暫存。
			dam = new DataAccessManager();
			String query4 = "select * from Customers where customer_id =:relateCustId ";
			dam.setObject("relateCustId", relateCustId);
			dam.setQueryString(query4);
			List<Map<String, Object>> list2 = dam.exeQuery();

			// 如果group_id不存在Groups的table中
			if (list1.isEmpty()) {
				// 在Groups的table中新增集團戶資料。
				dam = new DataAccessManager();
				String query5 = " insert into Groups (group_id, group_contact, group_phone, group_address, main_customer_id,created_time) "
						+ " values (:group_id, :group_contact, :group_phone, :group_address, :main_customer_id,getdate()) ";

				// 將撈出的新的主帳戶的客戶資料，mapping到SQL做新增集團戶資料的動作。
				for (Map<String, Object> map : list2) {
					dam.setObject("group_id", map.get("group_id"));
					dam.setObject("group_contact", map.get("customer_contact"));
					dam.setObject("group_phone", map.get("customer_phone"));
					dam.setObject("group_address", map.get("customer_address"));
					dam.setObject("main_customer_id", map.get("customer_id"));
				}
				dam.setQueryString(query5);
				dam.exeUpdate();

			}
			// 如果group_id存在Groups的table中
			else {
				// 更新集團戶資料的主帳資料。
				dam = new DataAccessManager();
				String query6 = " update Groups set group_contact =:group_contact, group_phone =:group_phone, group_address =:group_address, main_customer_id =:main_customer_id ,updated_time=getdate()"
						+ " where group_id =:group_id ";
				for (Map<String, Object> map : list2) {
					dam.setObject("group_id", map.get("group_id"));
					dam.setObject("group_contact", map.get("customer_contact"));
					dam.setObject("group_phone", map.get("customer_phone"));
					dam.setObject("group_address", map.get("customer_address"));
					dam.setObject("main_customer_id", map.get("customer_id"));
				}
				dam.setQueryString(query6);
				dam.exeUpdate();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 檢查是否為集團戶
	private void checkGroup(DataAccessManager dam, String custId) {
		// 檢查是否為集團戶
		try {
			StringBuffer query_sb1 = new StringBuffer(
					"select * from Customers where customer_id =:custId ");

			dam.setObject("custId", custId);
			String query_str1 = query_sb1.toString();
			dam.setQueryString(query_str1);
			List<Map<String, Object>> queryList1 = dam.exeQuery();
			System.out.println("===檢查是否為集團戶===");

			for (Map<String, Object> map1 : queryList1) {
				System.out.println("===is_group:" + map1.get("is_group"));
				// 如果不是集團戶
				if (StringUtils.equals((map1.get("is_group") + ""), "0")) {

					System.out.println("===如果不是集團戶===");
					System.out.println("===產生一個隨機的id 且不重複的 給他===");

					// 產生亂數id 並檢查有無重複，如果無重複，就傳回id。

					// String groupId_ran = checkDupId(dam, custId);

					String groupId_ran = getRandomGroupID();

					System.out.println("===groupId_ran:" + groupId_ran + "===");
					System.out.println("===custId:" + custId + "===");

					dam = new DataAccessManager();
					StringBuffer query_sb3 = new StringBuffer(
							"update Customers set group_id =:groupId_ran, is_group = '1',is_group_main = '1' where  customer_id=:custId ");
					dam.setObject("custId", custId);
					dam.setObject("groupId_ran", groupId_ran);
					String query_str3 = query_sb3.toString();
					dam.setQueryString(query_str3);
					dam.exeUpdate();

					// 更新Goup table的集團戶主帳戶資料
					updateGroupTable(dam, custId);
					// 確認主帳戶是否正在配發Token中
					Boolean flag = chkJobQs(custId);
					if(flag){
						updJobQs(custId,groupId_ran);						
					}else if(chkTokenCust(custId)){
						updTokenCust(custId,groupId_ran);
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 產生亂數給集團戶id使用
	private String getRandomGroupID() {
//		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmssSSS");
		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
		StringBuffer sb = new StringBuffer(sdf.format(new Date()));

		int num = (int) (Math.random() * 99 + 1);// 先產生4位數字的亂數,不然長度不夠裝

//		String rd = sb.deleteCharAt(14).toString() + String.valueOf(num);
		String rd = sb + String.valueOf(num);

		return rd;
	}

	public String addNewRelateAccountList(String newRelateCustId, String custId) {
		DataAccessManager dam = new DataAccessManager();
		try {
//			// 檢查畫面上排客戶是否為集團戶，並做更新。
//			checkGroup(dam, custId);

			dam = new DataAccessManager();
			StringBuffer query1 = new StringBuffer();
			query1.append("select *, ");
			query1.append("'token_esn' = ");
			query1.append("case ");
			query1.append("when ");
			query1.append("is_group = 0 ");
			query1.append("then (select token_esn from Token_Customer where related_to = c.customer_id) ");
			query1.append("else (select token_esn from Token_Customer where related_to = c.group_id) ");
			query1.append("end ");
			query1.append("from Customers c ");
			query1.append("where 1=1 ");
			query1.append("and c.customer_id = :custId ");
//			String query1 = "select is_group from Customers where customer_id =:newRelateCustId ";
			dam.setObject("custId", newRelateCustId);
//			dam.setQueryString(query1);
			List<addNewGroupVO> list1 = dam.exeQuery(query1.toString(),addNewGroupVO.class);

			for (addNewGroupVO vo : list1) {

				if (StringUtils.equals(vo.getIsGroup(), "0") && StringUtils.isBlank(vo.getTokenEsn())) {
					// 確認新增的集團戶是否正在配發Token中
					Boolean flag = chkJobQs(newRelateCustId);
					if(flag){
						errorMessage = "此客戶已申請Token配發中，不可新增";
					}else{
						System.out.println("vo.getTokenEsn() = "+vo.getTokenEsn());
						// 檢查畫面上排客戶是否為集團戶，並做更新。
						checkGroup(dam, custId);
						
						dam = new DataAccessManager();
						String query2 = " update Customers set group_id = (select group_id from Customers where customer_id =:custId ), "
								+ " is_group = '1' , is_group_main = '0' where customer_id =:newRelateCustId ";

						System.out.println("custId:" + custId);
						System.out.println("newRelateCustId:" + newRelateCustId);

						dam.setObject("custId", custId);
						dam.setObject("newRelateCustId", newRelateCustId);
						dam.setQueryString(query2);
						dam.exeUpdate();
					}
				} else if(StringUtils.equals(vo.getIsGroup(), "1")){
					errorMessage = "此客戶已有所屬集團戶，不可新增";
				} else if(StringUtils.isNotBlank(vo.getTokenEsn())){
					errorMessage = "此客戶已有配發Token esn不可變更集團戶狀態";
				}
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		
		System.out.println("errorMessage:"+errorMessage);
		
		
		return errorMessage;
	}
	
	public String getErrorMessage() {
		return errorMessage;
	}
	
	
}
